package transactions.dq;

import java.io.File;
import java.util.Date;
import jxl.*;
import dinamica.*;

public class ImportExcel extends GenericTableManager  {

	//variable que contendra el nombre de la columna que produjo la excepcion
	String columna = null;
	
	@Override
	public int service(Recordset inputParams) throws Throwable {
		
		int rc = super.service(inputParams);

		//crea un recordset para el manejo de los errores
		Recordset rsError = new Recordset();
				
		//define la estructura del recordset que contendra los errores
		rsError.append("columna", java.sql.Types.VARCHAR);
		rsError.append("fila", java.sql.Types.INTEGER);
		rsError.append("error", java.sql.Types.VARCHAR);
				
		//se alamacena en sesion el recordset de errores validacion
		getSession().setAttribute("error.excel",rsError);
		
		//registrar el numero de errores
		inputParams.setValue("total_errores", new Integer(rsError.getRecordCount()));
		
		//archivo nulo sera considerado un error
		if (inputParams.isNull("file.filename"))
			throw new Throwable("导入文件不能为空!");

		String fileName = (String) inputParams.getValue("file.filename");
		fileName = fileName.substring(fileName.lastIndexOf(File.separator) + 1);
		fileName = formatRequestEncoding(fileName);
		inputParams.setValue("file.filename", fileName);
		
		//obtener parametros del validator
		String file = inputParams.getString("file");
		
		//obtener el archivo Excel
		Workbook wb;
		
		try {
			wb = Workbook.getWorkbook(new File(file));
		} catch (JXLException e1) {
			throw new Throwable ("Formato Excel no reconocido; use Excel 97, XP o 2003",e1);
		}
		
		//obtener la hoja de calculo del archivo
		Sheet sheet = wb.getSheet(0);
		
		//obtener el numero de registros
		int numOfRows = sheet.getRows();
		
		Recordset rs = new Recordset();
					 
		rs.append("project_name", java.sql.Types.VARCHAR);
		rs.append("location_name", java.sql.Types.VARCHAR);
		rs.append("house_name", java.sql.Types.VARCHAR);
		rs.append("house_type", java.sql.Types.VARCHAR);
		rs.append("population", java.sql.Types.INTEGER);
		rs.append("house_area", java.sql.Types.DOUBLE);
		rs.append("house_number", java.sql.Types.DOUBLE);
		rs.append("xf_area", java.sql.Types.DOUBLE);

		int columnas = sheet.getColumns();

		if (columnas == 8)
		{
			//去掉首行，解析数据
			for(int i = 1; i<numOfRows;i++)
			{ 
				try
				{
					rs.addNew();
					for(int k=0;k<8;k++){
						Cell column = sheet.getCell(k,i);
						String content = column.getContents();
						columna = Integer.toString(k+1);
						switch(k){
							case 0:
								if(content == null || content.equals("")){
									throw new Throwable ("该单元格不能为空！");
								}
								rs.setValue("project_name", content);
								break;
							case 1:
								if(content == null || content.equals("")){
									throw new Throwable ("该单元格不能为空！");
								}
								rs.setValue("location_name", content);
								break;
							case 2:
								if(content == null || content.equals("")){
									throw new Throwable ("该单元格不能为空！");
								}
								rs.setValue("house_name", content);
								break;
							case 3:
								rs.setValue("house_type", content);
								break;
							case 4:
								Integer p = null;
								try{
									if(content != null && !content.equals("")){
										p = new Integer(Integer.parseInt(content));
									}
								}catch(Exception e){
									throw new Throwable ("该单元格格式不正确！");
								}
								rs.setValue("population", p);
								break;
							case 5:
								Double p1 = null;
								try{
									if(content != null && !content.equals("")){
										p1 = new Double(Double.parseDouble(content));
									}
								}catch(Exception e){
									throw new Throwable ("该单元格格式不正确！");
								}
								rs.setValue("house_area", p1);
								break;
							case 6:
								Double p2 = null;
								try{
									if(content != null && !content.equals("")){
										p2 = new Double(Double.parseDouble(content));
									}
								}catch(Exception e){
									throw new Throwable ("该单元格格式不正确！");
								}
								rs.setValue("house_number", p2);
								break;
							case 7:
								Double p3 = null;
								try{
									if(content != null && !content.equals("")){
										p3 = new Double(Double.parseDouble(content));
									}
								}catch(Exception e){
									throw new Throwable ("该单元格格式不正确！");
								}
								rs.setValue("xf_area", p3);
								break;
						}
					}
				}
				catch (Throwable a)
				{
					rsError.addNew();
					rsError.setValue("columna", columna);
					rsError.setValue("fila", (i+1));
					rsError.setValue("error", a.getMessage());

					inputParams.setValue("total_errores", new Integer(rsError.getRecordCount()));

					if (rsError.getRecordCount()==20)
					{
						getSession().setAttribute("error.excel",rsError);
						throw new Throwable("Excel文件包含错误已经超过 20 处！");
					}
				}
			}
		}
		else
		{
			throw new Throwable("Excel格式不正确，应该为 8 列！ "); 
		}
		
		if (rsError.getRecordCount()>0) 
		{
			getSession().setAttribute("error.excel",rsError);
			throw new Throwable("Excel文件包含错误！");
		}
						
		inputParams.setValue("total_registros", new Integer(rs.getRecordCount()));
		
		Db db = getDb();
		String insert = getResource("insert.sql");
		rs.top();
		while(rs.next()){
			String sql = getSQL(insert, rs);
			db.addBatchCommand(sql);
		}
		db.exec();
		
		return rc;
	}

	protected String formatRequestEncoding(String str) throws Throwable {

        Config _config = getConfig();

        // global encoding?
        String encoding = getContext().getInitParameter("request-encoding");
        if (encoding != null && encoding.trim().equalsIgnoreCase(""))
            encoding = null;

        // load resource with appropiate encoding if defined
        if (_config.requestEncoding != null)
            return new String(str.getBytes("ISO8859-1"), _config.requestEncoding);
        else if (encoding != null)
            return new String(str.getBytes("ISO8859-1"), encoding);
        else
            return str;
    }

}
